﻿<%@ WebHandler Language="C#" Class="APSPlanDataRecycleDashBoard" %>

using System;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;
using System.Collections;
using System.IO;

/**
 * Description: 三天锁排回收率统计看板
 * Copyright (c): 通力凯顿（北京）系统集成有限公司
 * Author: zhunan
 * Date: 2025.5.12
 */

public class APSPlanDataRecycleDashBoard : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getRecycleRateData":
                        r = getRecycleRateData(jObject);//排产计划回收率统计
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getDetailsData":
                        r = getDetailsData(jObject);//数据钻取
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportExcel":
                        exportExcel(jObject);//主界面表格数据导出
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportDetailsData":
                        exportDetailsData(jObject);//对话框表格明细导出
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    string getApsSql(JObject obj)
    {
        string sql = @"
SELECT f.factoryname,w.workcentername,te.teamname,cci.familylistnames,cci.productname,cci.mfgordername,
       cci.containername,aps.workflowstepname,wfb.workflowname,wf.workflowrevision,aps.starttime,aps.endtime
FROM apsplandetail aps
LEFT JOIN containercurrentinfo cci ON cci.containerid = aps.containerid
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
INNER JOIN workcenter w ON w.workcenterid = aps.workcenterid
LEFT JOIN team te ON te.teamid = aps.teamid
LEFT JOIN workflowstep ws ON ws.workflowstepid = aps.workflowstepid
LEFT JOIN workflow wf ON wf.workflowid = ws.workflowid
LEFT JOIN workflowbase wfb ON wfb.workflowbaseid = wf.workflowbaseid
WHERE aps.isuse = 0";
        //分厂
        if (obj["factoryid"] != null && obj["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", obj["factoryid"].ToString());
            if (obj["deptname"] != null && obj["deptname"].ToString() != "")
            {
                sql += string.Format(" AND w.workcentername = '{0}'", obj["deptname"].ToString());
            }
        }
        else
        {
            if (obj["deptname"] != null && obj["deptname"].ToString() != "")
            {
                sql += string.Format(" AND f.factoryname = '{0}'", obj["deptname"].ToString());
            }
        }

        //批次号
        if (obj["containername"] != null && obj["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", obj["containername"].ToString());
        }

        //零件图号
        if (obj["productname"] != null && obj["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", obj["productname"].ToString());
        }

        //订单号
        if (obj["mfgordername"] != null && obj["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND cci.mfgordername = '{0}'", obj["mfgordername"].ToString());
        }

        //计划完成时间
        if (obj["endtime"] != null && obj["endtime"].ToString() != "")
        {
            JArray arr = JArray.Parse(obj["endtime"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND aps.endtime >= to_date('{0} 00:00:00','yyyy-MM-dd hh24:mi:ss')", arr[0].ToString());
                sql += string.Format(" AND aps.endtime <= to_date('{0} 23:59:59','yyyy-MM-dd hh24:mi:ss')", arr[1].ToString());
            }
        }
        return sql;
    }

    string getApsRecordChangeSql(JObject obj)
    {
        string sql = @"
SELECT cci.containername,ws.workflowstepname,e.fullname,t.updatedate,cci.familylistnames,cci.productname,
       cci.productdescription,cci.mfgordername,
       wfb.workflowname,wf.workflowrevision,aps.endtime,aps.starttime,f.factoryname,w.workcentername,te.teamname,to_char(wt.sendxml) reason,
       row_number () OVER (PARTITION BY t.containerid,t.workflowstepid ORDER BY t.updatedate DESC) as rn
FROM apsplanupdaterecord t
LEFT JOIN containercurrentinfo cci ON cci.containerid = t.containerid
LEFT JOIN employee e ON e.employeeid = t.updateempid
LEFT JOIN workflowstep ws ON ws.workflowstepid = t.workflowstepid
LEFT JOIN workflow wf ON wf.workflowid = ws.workflowid
LEFT JOIN workflowbase wfb ON wfb.workflowbaseid = wf.workflowbaseid
LEFT JOIN apsplandetail aps ON aps.containerid = t.containerid AND aps.workflowstepid = t.workflowstepid AND aps.isuse = 0
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
INNER JOIN workcenter w ON w.workcenterid = aps.workcenterid
LEFT JOIN team te ON te.teamid = aps.teamid
LEFT JOIN (SELECT * FROM writetoothersystem wtt WHERE wtt.recordtype ='Aps_ToExamineRecall'
      AND EXISTS (SELECT 1 FROM writetoothersystem wtt2 WHERE wtt2.containerid=wtt.containerid AND wtt2.parentid =wtt.parentid AND wtt2.recordtype='Aps_ToAgreeRecall' AND wtt2.issuccess=0)
) wt ON wt.containerid=cci.containerid AND wt.parentid =aps.apsplandetailid
WHERE t.status = 2";
        //分厂
        if (obj["factoryid"] != null && obj["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND f.factoryid = '{0}'", obj["factoryid"].ToString());
            if (obj["deptname"] != null && obj["deptname"].ToString() != "")
            {
                sql += string.Format(" AND w.workcentername = '{0}'", obj["deptname"].ToString());
            }
        }
        else
        {
            if (obj["deptname"] != null && obj["deptname"].ToString() != "")
            {
                sql += string.Format(" AND f.factoryname = '{0}'", obj["deptname"].ToString());
            }
        }

        //批次号
        if (obj["containername"] != null && obj["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", obj["containername"].ToString());
        }

        //零件图号
        if (obj["productname"] != null && obj["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", obj["productname"].ToString());
        }

        //订单号
        if (obj["mfgordername"] != null && obj["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND cci.mfgordername = '{0}'", obj["mfgordername"].ToString());
        }

        //回收时间
        if (obj["endtime"] != null && obj["endtime"].ToString() != "")
        {
            JArray arr = JArray.Parse(obj["endtime"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND aps.endtime >= to_date('{0} 00:00:00','yyyy-MM-dd hh24:mi:ss')", arr[0].ToString());
                sql += string.Format(" AND aps.endtime <= to_date('{0} 23:59:59','yyyy-MM-dd hh24:mi:ss')", arr[1].ToString());
                sql += string.Format(" AND t.updatedate >= to_date('{0} 00:00:00','yyyy-MM-dd hh24:mi:ss') - 4", arr[0].ToString());
                sql += string.Format(" AND t.updatedate <= to_date('{0} 23:59:59','yyyy-MM-dd hh24:mi:ss')", arr[1].ToString());
            }
        }
        return sql;
    }

    Result getData(JObject jObject)
    {
        string sql = getApsRecordChangeSql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dtResult = dto.DBTable;

        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //回收率统计
    Result getRecycleRateData(JObject obj)
    {
        string sql = "";
        if (obj["factoryid"] != null && obj["factoryid"].ToString() != "")
        {
            sql = @"
SELECT t.workcentername deptname,
       COUNT(1) apsqty,
       NVL(mm.changeqty,0) changeqty,
       ROUND(NVL(mm.changeqty,0)/COUNT(1),4) * 100 changeRate
FROM (
  {0}
) t
LEFT JOIN (
    SELECT m.workcentername,COUNT(1) changeqty
    FROM (
        {1}
    ) m
    WHERE m.rn = 1
    GROUP BY m.workcentername
) mm ON mm.workcentername = t.workcentername
GROUP BY t.workcentername,mm.changeqty";
        }
        else
        {
            sql = @"
SELECT t.factoryname deptname,
       COUNT(1) apsqty,
       NVL(mm.changeqty,0) changeqty,
       ROUND(NVL(mm.changeqty,0)/COUNT(1),4) * 100 changeRate
FROM (
  {0}
) t
LEFT JOIN (
    SELECT m.factoryname,COUNT(1) changeqty
    FROM (
        {1}
    ) m
    WHERE m.rn = 1
    GROUP BY m.factoryname
) mm ON mm.factoryname = t.factoryname
GROUP BY t.factoryname,mm.changeqty";
        }

        sql = string.Format(sql, getApsSql(obj), getApsRecordChangeSql(obj));

        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    Result getDetailsData(JObject obj)
    {
        string sql = "";
        if (obj["seriesName"].ToString() == "排产工序数")
        {
            sql = getApsSql(obj);
        }
        else
        {
            sql = getApsRecordChangeSql(obj);
        }
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)obj["currentPage"], (int)obj["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    void exportExcel(JObject obj)
    {
        string sql = getApsRecordChangeSql(obj);
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        dtResult.Columns["factoryname"].Caption = "主制厂";
        dtResult.Columns["workcentername"].Caption = "中心";
        dtResult.Columns["teamname"].Caption = "单元";
        dtResult.Columns["fullname"].Caption = "回收人";
        dtResult.Columns["updatedate"].Caption = "回收时间";
        dtResult.Columns["familylistnames"].Caption = "型别";
        dtResult.Columns["productname"].Caption = "零件号";
        dtResult.Columns["productdescription"].Caption = "零件名称";
        dtResult.Columns["mfgordername"].Caption = "订单号";
        dtResult.Columns["containername"].Caption = "批次号";
        dtResult.Columns["workflowstepname"].Caption = "工序名称";
        dtResult.Columns["workflowname"].Caption = "工艺路线";
        dtResult.Columns["workflowrevision"].Caption = "工艺版本";
        dtResult.Columns["starttime"].Caption = "计划开始时间";
        dtResult.Columns["endtime"].Caption = "计划完成时间";

        ExportHelper.ExportExcel(dtResult, "三天锁排回收率统计看板导出数据");
    }

    void exportDetailsData(JObject obj)
    {
        string sql = "";
        if (obj["seriesName"].ToString() == "排产工序数")
        {
            sql = getApsSql(obj);
        }
        else
        {
            sql = getApsRecordChangeSql(obj);
        }

        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        if (dtResult.Columns.Contains("fullname"))
        {
            dtResult.Columns["fullname"].Caption = "回收人";
            dtResult.Columns["updatedate"].Caption = "回收时间";
            dtResult.Columns.Remove("rn");
        }

        if (dtResult.Columns.Contains("starttime"))
        {
            dtResult.Columns["starttime"].Caption = "计划开始时间";
            dtResult.Columns["endtime"].Caption = "计划完成时间";
        }

        dtResult.Columns["factoryname"].Caption = "主制厂";
        dtResult.Columns["workcentername"].Caption = "中心";
        dtResult.Columns["teamname"].Caption = "单元";
        dtResult.Columns["familylistnames"].Caption = "型别";
        dtResult.Columns["productname"].Caption = "零件号";
        dtResult.Columns["productdescription"].Caption = "零件名称";
        dtResult.Columns["mfgordername"].Caption = "订单号";
        dtResult.Columns["containername"].Caption = "批次号";
        dtResult.Columns["workflowstepname"].Caption = "工序名称";
        dtResult.Columns["workflowname"].Caption = "工艺路线";
        dtResult.Columns["workflowrevision"].Caption = "工艺版本";

        ExportHelper.ExportExcel(dtResult, obj["title"].ToString());
    }
}